#!/usr/bin/env python
#coding=utf-8
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relation, backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey,create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import join
from sqlalchemy.sql import func
from sqlalchemy.orm import mapper, relation
engine = create_engine('sqlite:///SimpleSite/development.db', echo=True)
metadata = MetaData()


# Tables
groups_table = Table("groups", metadata,
        Column("uid",Integer,primary_key=True),
        Column("name",String(255),unique=True,nullable=False),
    )

roles_table = Table("roles",metadata,
       Column("uid",Integer,primary_key=True),
       Column("name",String(255),unique=True,nullable=False),
   )

users_table = Table("users",metadata,
    Column("uid",Integer,primary_key=True),
    Column("username",String(255),unique=True,nullable=False),
    Column("password",String(255),nullable=False),
    Column("group_uid",Integer,ForeignKey("groups.uid")),
)
users_roles_table = Table("users_roles",metadata,
    Column("user_uid",Integer,ForeignKey("users.uid")),
    Column("role_uid",Integer,ForeignKey("roles.uid")),
)

#创建表
metadata.create_all(engine) 

class User(object):
    def __init__(self,username,uid=None,password=None,group_uid=None,):
        self.uid        = uid
        self.username   = username
        self.password   = password
        self.group_uid  = group_uid
    def __repr__(self):
        return "User(%(username)s)" % self.__dict__

class Group(object):
    def __init__(self, name=None):
        self.name = name
    def __repr__(self):
        return "Group(%(name)s)" % self.__dict__

class Role(object):
    def __init__(self, name=None):
        self.name = name
    def __repr__(self):
        return "Role(%(name)s)" % self.__dict__
groups_mapper = mapper(Group,groups_table,
    properties={"users": relation(User)})

users_mapper = mapper(User,users_table,
    properties={
        "roles": relation(Role, lazy=True, secondary=users_roles_table),
        "group": relation(Group),
    }
)
roles_mapper = mapper(Role,roles_table,
      properties={
          "users": relation(User, lazy=True, secondary=users_roles_table)
      }
  )
Session = sessionmaker(bind=engine)
session=Session()

# 增加一个组为 "manager",其组下增加一个用户名为”sam",其权限为“view",
group_s=Group('manager')#增加一个组
#user=User(username="sam",password="ccs")
#user.roles.append(Role("view"))
#group_s.users.append(user)
#session.add(group_s)

ss=session.query(User).filter_by(username='admin').first()
session.delete(ss)
session.commit()

